#!/usr/bin/python

from pysqlite2 import dbapi2 as sqlite3
from datetime import datetime
import os

#Contains the important database tables
#Cache is in seperate sql files
#Details is pulled from scraper.GetDetails, and is in xml format pulled from the Cache.


#Example adding data:
#pull data from a movies directory
#find directory, add Movie(name="directory"), go through and add Files for all files in that directory that's a media file.
#  - scan .nfo file in directory to locate link, if no link, we dont have details for the movie and url = NULL
#  - scraper.Search(directoryName), present results to user, pick desired movie and update url.
#  - on update url GetDetails, GetDetails pulls the web page down into cache and adds MovieDetails/Tags etc

#Example viewing data:
#main menu should have all important details about the movie
#on GetDetails GetDetails calls HTTP_Get, which pulls the details from cache, and presents movie details to user

MediaTypes=[".mkv",".avi"]

class Content:
	def __init__(self,debug=False):
		self.debug = debug
		if (debug):
			print "Content : using database :memory:"
			self.conn = sqlite3.connect(':memory:')
		else:
			print "Content : using database database.sql"
			self.conn = sqlite3.connect('database.sql')
		self.createTables()
		
	def createTables(self):
		try:
			#Table CONTENT:
			#name - Displayed name
			#updated - Date content was updated (used for sorting), can be new file, can be new Part
			#
			#url - url to pull data from, for example http://www.themoviedb.org/movie/1858
			#released - Date content was released (first_aired for series)
			#rating - content rating
			#plot_short - A short description of the content
			#plot_long - A long description (on themoviedb.org this is wiki info
			#ended - Series only, when series ended
			#status - Series only, can be running/cancelled etc
			self.conn.execute("""CREATE TABLE Content (
				id INTEGER NOT NULL PRIMARY KEY, 
				title TEXT NOT NULL,
				updated DATE,
				
				released DATE,
				rating REAL,
				plot_short TEXT,
				plot_long TEXT,
				ended DATE,
				status TEXT)""")
				
			self.conn.execute("""CREATE TABLE Urls (
				contentId INTEGER NOT NULL REFERENCES Content (id),
				url TEXT)""")
				
			
			#Part can be episode or movie part (movies should always have 1 part unless it's special stuff)
			#season = NULL when special/movie part.
			#part == episode
			#this will ONLY be updated from the web
			self.conn.execute("""CREATE TABLE Part (
				contentId INTEGER NOT NULL REFERENCES Content (id),
				season INTEGER,
				part INTEGER NOT NULL,
				seen BOOL NOT NULL)""")
				
			#Directories linked with content, if the file exists locally, they are scanned and files added to the Files table.
			# The scanner goes through and checks modification date on the files, and sync's updated to that date.
			self.conn.execute("""CREATE TABLE Directory (
				id INTEGER NOT NULL PRIMARY KEY, 
				contentId INTEGER NOT NULL REFERENCES content (id),
				path TEXT NOT NULL,
				updated DATE)""")
				
			#Local files assosiated with content
			# filename = /Season 1/blabla.filenameS01E05.avi
			self.conn.execute("""CREATE TABLE Files (
				contentId INTEGER NOT NULL REFERENCES content (id),
				filename TEXT NOT NULL,
				directoryId INTEGER NOT NULL REFERENCES Directory (id))""")
			#TODO : partID INTEGER REFERENCES Part (id) - but gotta update this as often as possible. this is to help queries such as select * from files where partid=NULL
				
			#Alternate titles:
			self.conn.execute("""CREATE TABLE Titles (
				contentId INTEGER NOT NULL REFERENCES content (id),
				title TEXT NOT NULL)""")
			
			#Trailers:
			self.conn.execute("""CREATE TABLE Trailers (
				contentId INTEGER NOT NULL REFERENCES content (id),
				url TEXT NOT NULL)""")
			
			#Tag system, category/plot keywords etc:
			#Example tagTypes:
			#NULL - user created
			#plot - plot tags
			#TheMovieDB-Category - specific customized tag
			#Files - for file quality tags
			self.conn.execute("""CREATE TABLE Tag (
				id INTEGER NOT NULL PRIMARY KEY,
				name TEXT NOT NULL UNIQUE,
				tagType TEXT)""") 
				
			#TagLinks, linking tags to content
			self.conn.execute("""CREATE TABLE TagLink (
				contentId INTEGER NOT NULL REFERENCES content (id),
				tagId INTEGER NOT NULL REFERENCES Tag (id))""")
				
			print "Created default tables ok"
			self.conn.commit()
		except Exception,e:
			if self.debug:
				print "EXCEPTION in table generation",e
				raise Exception("Exception in table generation",e)
		if self.debug:
			print "Adding some fake content"
			self.AddContent({'title':"Transformers",'rating':7.3})
			self.AddContent({'title':"Fight club",'rating':8.3})
			self.AddContent({'title':"Matrix",'rating':9.3})
			
			
	#			self.conn.execute("""CREATE TABLE Directory (
	#			contentId INTEGER NOT NULL REFERENCES content (id),
	#			filename TEXT NOT NULL,
	#			directory TEXT NOT NULL,
	#			fullPath TEXT NOT NULL,
	#			updated DATE)""")
	
	
	def AddUrl(self,contentId,url):
		x = self.conn.execute("SELECT contentId FROM Urls WHERE contentId=? AND url=?",(contentId,url,)).fetchone()
		if not x:
			self.conn.execute("INSERT INTO Urls VALUES (?,?)",(contentId,url,))
	
	def GetTag(self,tagName,tagType):
		x = self.conn.execute("SELECT id FROM Tag WHERE name=?",(tagName,)).fetchone()
		if x:
			return x[0]
		c = self.conn.cursor()
		c.execute("INSERT INTO Tag VALUES (NULL,?,?)",(tagName,tagType,))
		return c.lastrowid

	def LinkTag(self,contentId,tagId):
		if not self.conn.execute("SELECT contentId from TagLink WHERE contentId=? AND tagId=?",(contentId,tagId)).fetchone():
			self.conn.execute("INSERT INTO TagLink VALUES (?,?)",(contentId,tagId,))
	
	def GetDirectory(self,path):
		c = self.conn.cursor()
		c.execute("SELECT * FROM Directory WHERE path=?",(path,))
		return c.fetchone()
		
	def AddDirectory(self,contentId,path):
		c = self.conn.cursor()
		c.execute("INSERT INTO Directory VALUES (NULL,?,?,?)",(contentId,path,datetime.now()))
		return c.lastrowid
		
	#Scans a directory for new files:
	def ScanDirectory(self,directoryId):
		contentId,path = self.conn.cursor().execute("SELECT contentId,path FROM Directory WHERE id=?",(directoryId,)).fetchone()
		
		for (dirpath, dirnames, filenames) in os.walk(path):
			for filename in filenames:
				if os.path.splitext(filename)[1] in MediaTypes:
					filename = os.path.join(dirpath[len(path) + 1:],filename)
					#Check if filename exists:
					if not self.conn.execute("SELECT contentId FROM Files WHERE directoryId=? AND filename=?",(directoryId,filename,)).fetchone():
						self.conn.execute("INSERT INTO Files VALUES (?,?,?)",(contentId,filename,directoryId,))
		#print "ContentId:",contentId,path
		
	def AddContent(self,data):
		def getKey(key):
			if data.has_key(key):
				return data[key]
			return None
		#Should only be used internally.
		c = self.conn.cursor()
		#accessing data['title'] manually as that field must exist.
		c.execute("INSERT INTO Content VALUES (NULL,?,?,?,?,?,?,?,?)",(data['title'],datetime.now(),getKey('released'),getKey('rating'),getKey('plot_short'),getKey('plot_long'),getKey('ended'),getKey('status')))
		return c.lastrowid
		
	def UpdateDetails(self,contentId,data):
		def getKey(key):
			if data.has_key(key):
				return data[key]
			return None
		self.conn.execute("UPDATE Content SET title=?, updated=?, released=?, rating=?, plot_short=?, plot_long=?, ended=?, status=? WHERE id=?",(data['title'],datetime.now(),getKey('released'),getKey('rating'),getKey('plot_short'),getKey('plot_long'),getKey('ended'),getKey('status'),contentId))
	
	def AddPart(self,contentId,season,part):
		self.conn.execute("INSERT INTO Part VALUES(?,?,?,0)",(contentId,season,part,))
		
	# - DEBUG
	def PrintContent(self):
		c = self.conn.cursor()
		c.execute("select * from Content order by id")
		for row in c:
			print row
			
		print "Count of Files:"
		print c.execute("select count(*) from Files").fetchone()[0]

